package com.sxc.OnlineStore.user.dao;

import com.sxc.OnlineStore.Utils.ConnectMySql;
import com.sxc.OnlineStore.user.domain.User;

import java.sql.*;

public class UserIm {
    private Connection conn = ConnectMySql.conMysql();//定义好数据库对象
    private CallableStatement cs = null; //初始化调用存储过程
    private ResultSet rs = null; //定义rs
    private PreparedStatement ps = null;
    private String sql = null;
    private int userPK = 0;

    public boolean confirmUser (String userName, String userPW){
        boolean flag = false;

        try {
            User user = new User();
            sql = "SELECT\n" +
                    "\t userAcco_ID,\n" +
                    "\t userAcco_Name,\n" +
                    "\t userAcco_PW \n" +
                    "FROM\n" +
                    "\t useraccount \n" +
                    "WHERE\n" +
                    "\t userAcco_Name = '"+userName+"'";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                user.setUser_PW(rs.getString(3));

                if ( userPW.equals(user.getUser_PW()) ){
                    flag = true;//密码正确

                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return flag;//密码或账号错误
    }

    public boolean QueryUser(String userName){
        boolean flag = true;//假设用户不存在
        try {
            sql = "SELECT COUNT(*) FROM useraccount WHERE userAcco_Name = '"+userName+"'";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()){
                int count = rs.getInt(1);
                if (count > 0){
                    flag = false;//用户已经存在了
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return flag;
    }

    public void AddUser( String userName, String userPW ){
        try {
            User user = new User();
            cs = conn.prepareCall("call P_addUser(?,?)");
            //传值给存储过程
            cs.setString(1,userName);
            cs.setString(2,userPW);

            rs = cs.executeQuery();//执行存储过程，并添加用户

            user.setUser_Name(userName);//对user_Name属性赋值
            user.setUser_PW(userPW);    //对user_PW属性赋值

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public int GetUserPK ( String userName ){

        try {
            sql = "SELECT ua.userAcco_ID FROM useraccount as ua WHERE ua.userAcco_Name = '"+userName+"'";
            ps = conn.prepareStatement(sql);

            rs = ps.executeQuery();

            while (rs.next()){
                userPK = rs.getInt(1);//获取user表的主键PK
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }


        return userPK;//获取user表的主键
    }

/*尝试使用存储过程但是获取到的查询为空~~
public int confirmUser ( String userID,String userPW){
      try {
          User user = new User();

          cs = conn.prepareCall("{call confirmUser(?)}");
          cs.setString(1,userID);//传值给存储过程
          rs = cs.executeQuery();//执行存储过程

          if (rs.next()){
              user.setUser_PW(cs.getString(1));//得到数据库中的密码user_PW

              if (userPW.equals(rs.getString(1))){
                    return 1;//密码正确
              }
                else return 0;//密码错误
          }


      } catch (SQLException e) {
          e.printStackTrace();
      }

      //关闭连接
      try {
          cs.close();
          conn.close();

      } catch (SQLException e) {
          e.printStackTrace();
      }

      return 0;
  }*/





}
